package MainProject;
import java.awt.Color;
import java.awt.Container;
import java.awt.FlowLayout;
import java.io.*;
import java.sql.*;
import javax.swing.*;
import javax.swing.table.*;
import javax.swing.event.*;
/**
 *
 * @author  hbargi
 */
public class TableForm extends javax.swing.JFrame {
	
	 Object[][] MyArray=null;
	    String [] AttributeNames;
	    Connection conn;
	    Statement stmt;
	    String state;
	    int NumberOfRows,NumberOfColumns;

	    private javax.swing.JScrollPane jScrollPane1;
	    private javax.swing.JTable DataBaseTable;
	    private javax.swing.JComboBox courselist;
	    private JLabel label;
	    private JLabel usernamelbl;
	    private JTextField usernametxt;
	    private JLabel gradelbl;
	    private JTextField gradetxt; 
	    private JButton submitbtn; 
	    private JLabel statuslbl;
	    private JPanel profpnl;

    public TableForm() {
        initComponents();
        DataBaseTable.setVisible(false);
        try {
            Class.forName("org.sqlite.JDBC");
           
            conn=DriverManager.getConnection(
                        "jdbc:sqlite:/Users/hbargi/MySQLiteDB");
            stmt=conn.createStatement();
        }
        catch(Exception e) {
            e.printStackTrace();
        }
        setSize(500,600);
    }


    private void initComponents() {//GEN-BEGIN:initComponents
    	profpnl = new JPanel();
        profpnl.add(jScrollPane1 = new javax.swing.JScrollPane());
        profpnl.add(DataBaseTable = new javax.swing.JTable());
        profpnl.add(label = new JLabel("Please select a course number"));
        profpnl.add(courselist =  new JComboBox());
        profpnl.add(usernamelbl = new JLabel());
        profpnl.add(usernametxt = new JTextField());
        profpnl.add(gradelbl = new JLabel());
        profpnl.add(gradetxt = new JTextField());
        profpnl.add(submitbtn = new JButton());
        profpnl.add(statuslbl = new JLabel());
        
        //jScrollPane2 = new javax.swing.JScrollPane();
        //ActionTextArea = new javax.swing.JTextArea();
        
        getContentPane().setLayout(null);
        
        addWindowListener(new java.awt.event.WindowAdapter() {
            public void windowClosing(java.awt.event.WindowEvent evt) {
                exitForm(evt);
            }
        });
        
        DataBaseTable.setModel(new javax.swing.table.DefaultTableModel(
            new Object [][] {
                {null},
                {null},
                {null},
                {null}
            },
            new String [] { ""}
            ) {
              Class[] types = new Class [] { java.lang.Object.class};
              boolean[]canEdit=new boolean[]{false};
                    
              public Class getColumnClass(int columnIndex) {
                    return types [columnIndex];
              }
            
              public boolean isCellEditable(int rowIndex, int columnIndex) {
                    return canEdit [columnIndex];
              }
        });

        DataBaseTable.addMouseListener(new java.awt.event.MouseAdapter() {
            public void mouseReleased(java.awt.event.MouseEvent evt) {
                DataBaseTableMouseReleased(evt);
            }
        });
        
        jScrollPane1.setViewportView(DataBaseTable);
        
        getContentPane().add(jScrollPane1);
        jScrollPane1.setBounds(3, 120, 460, 150);
        
      
        

	    courselist.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                QueryTable(evt);
            }
        });
	    getContentPane().add(label);
	    label.setBounds(10,10,200,50);
	    
	    
        getContentPane().add(courselist);
        courselist.setBounds(10, 45, 93, 27);
        //getContentPane().add(label);
        
        getContentPane().add(usernamelbl);
        usernamelbl.setText("Student name");
	    usernamelbl.setBounds(10,280,200,30);
	    getContentPane().add(usernametxt);
	    usernametxt.setBounds(100,280,200,30);
	    //usernametxt.setText(new MyTableModel().getValueAt(0, 1).toString());
	    
	    getContentPane().add(gradelbl);
	    gradelbl.setText("Grade");
	    gradelbl.setBounds(10,310,200,30);
	    getContentPane().add(gradetxt);
	    gradetxt.setBounds(100,310,200,30);
	    
	    getContentPane().add(submitbtn);
	    submitbtn.setText("Submit/Update Grade");
	    submitbtn.setBounds(100,340,200,60);
	    
	    getContentPane().add(statuslbl);
	    statuslbl.setText("");
	    statuslbl.setBounds(100,400,300,30);
	    
	    
	    
	    submitbtn.addActionListener(new java.awt.event.ActionListener() {
	        public  void actionPerformed(java.awt.event.ActionEvent evt) {
	        	try {
					submit_grades(evt);
				} catch (ClassNotFoundException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
	            }
	        });
  
        //jScrollPane2.setViewportView(ActionTextArea);
        
//        getContentPane().add(jScrollPane2);
//        jScrollPane2.setBounds(3, 273, 460, 110);
//        
        pack();
    }//GEN-END:initComponents
    
    public void submit_grades(java.awt.event.ActionEvent evt) throws ClassNotFoundException
    {
    	try{
    	Class.forName("org.sqlite.JDBC");
	    Connection conn =
	      DriverManager.getConnection("jdbc:sqlite:/Users/hbargi/MySQLiteDB");
	    Statement stat = conn.createStatement();
	    String query = "update grade_user" +
	    		" set grade_id = '" + gradetxt.getText() + 
	    		"' where userid = '" + usernametxt.getText() +"'";  
	   
	    int result = stat.executeUpdate(query);
	    //txtlbl2.setText(usertxt.getText());
	    if (result !=0){
	    	 statuslbl.setOpaque(true);
	 	    statuslbl.setBackground(Color.YELLOW);
	 	   statuslbl.setForeground(Color.BLACK);
	    	statuslbl.setText("Grade is submitted successfully..");
	    }
	    else{
	    	 statuslbl.setOpaque(true);
	 	    statuslbl.setBackground(Color.RED);
	 	   statuslbl.setForeground(Color.WHITE);
	    	statuslbl.setText("Whoops, something might happen..");
	    }
	    conn.close();
    	}
    	
    	catch (SQLException e){
    		e.printStackTrace();
    	}
    }
    
    public void update_grades(java.awt.event.ActionEvent evt) throws ClassNotFoundException
    {
    	try{
        	Class.forName("org.sqlite.JDBC");
    	    Connection conn =
    	      DriverManager.getConnection("jdbc:sqlite:/Users/hbargi/MySQLiteDB");
    	    Statement stat = conn.createStatement();
    	    String query = "update grade_user" +
    	    		" set grade_id = '" + gradetxt.getText() + 
    	    		"' where userid = '" + usernametxt.getText() +"'";  
    	   
    	    int result = stat.executeUpdate(query);
    	    //txtlbl2.setText(usertxt.getText());
    	    if (result !=0)
    	    	statuslbl.setText("Grade is submitted successfully..");
    	    else
    	    	statuslbl.setText("Whoops, something might happen..");
    	    
    	    conn.close();
        	}
        	catch (SQLException e){
        		e.printStackTrace();
        	}   	 
        }
      

    private void DataBaseTableMouseReleased(java.awt.event.MouseEvent evt) {
        usernametxt.setText(new MyTableModel().getValueAt(DataBaseTable.getSelectedRow(), DataBaseTable.getSelectedColumn()).toString());
       
//        append(DataBaseTable.getSelectedRow()+
//                "  "+DataBaseTable.getSelectedColumn()+'\n');
    }

    
    public void get_course() throws Exception
	{	
		// connection to SQLITE	
		Class.forName("org.sqlite.JDBC");
	    Connection conn =
	    DriverManager.getConnection("jdbc:sqlite:/Users/hbargi/MySQLiteDB");
	    Statement stat = null;
	    stat = conn.createStatement();
	    String query = "select course_id from courses";
	    ResultSet rs = stat.executeQuery(query);
	    // Get the data and put them in the combo-box
	    while(rs.next())
	    {
	    	courselist.addItem(rs.getString("course_id"));
	    }
	    // close DB connection
	    rs.close();
	    conn.close();
	    }
    
    
    private void QueryTable(java.awt.event.ActionEvent evt) {
        try {
            ResultSet res=stmt.executeQuery("select userid, course_id, grade_id from grade_user u" +
            		"  where course_id='" + courselist.getSelectedItem() + "'");
            ResultSetMetaData RSMD=res.getMetaData();
            NumberOfColumns=RSMD.getColumnCount();
            AttributeNames=new String[NumberOfColumns];
            for(int i=0;i<NumberOfColumns;i++)
                AttributeNames[i]=RSMD.getColumnName(i+1);
            MyArray=new Object[10000][NumberOfColumns];
            int R=0;
            while(res.next()) {
                for(int C=1; C<=NumberOfColumns;C++)
                    MyArray[R][C-1]=res.getObject(C);
                R++;
            }
            res.close();
            NumberOfRows=R;
            Object[][] TempArray=MyArray;
            MyArray=new Object[NumberOfRows][NumberOfColumns];
            for(R=0;R<NumberOfRows;R++)
                for(int C=0;C<NumberOfColumns;C++)
                    MyArray[R][C]=TempArray[R][C];
            DataBaseTable.setModel(new MyTableModel());
            DataBaseTable.setVisible(true);
        }
        catch(Exception e) {
            e.printStackTrace();
        }
    }

    /** Exit the Application */
    private void exitForm(java.awt.event.WindowEvent evt) {//GEN-FIRST:event_exitForm
        System.exit(0);
    }

    //private javax.swing.JScrollPane jScrollPane2;
    //private javax.swing.JTextArea ActionTextArea;
    

    class MyTableModel extends AbstractTableModel {
       //You can choose any long value for serialVersionUID.
       //I chose 317 because this is CSCI 317.
        private static final long serialVersionUID=317L;
        public int getColumnCount() {
            return (NumberOfColumns);
        }
        public int getRowCount() {
            return(NumberOfRows);
        }
        public String getColumnName(int i) {
            return (AttributeNames[i]);
        }
        public java.lang.Object getValueAt(int row, int column) {
            return(MyArray[row][column]);
        }
        public Class getColumnClass(int c) {
            return getValueAt(0, c).getClass();
        }
        public boolean isCellEditable(int row, int col) {
            //Note that the data/cell address is constant,
            //no matter where the cell appears onscreen.
            if (col < 3) { 
                return false;
            } else {
                return true;
            }
        }
        public void setValueAt(Object value, int row, int col) {
            MyArray[row][col] = value;
            fireTableCellUpdated(row, col);
        }        
    }
    
    public static void main(String args[]) throws Exception {
    	TableForm tf = new TableForm();
    	tf.setVisible(true);
    	tf.get_course();
    }
}

